﻿using MySql.Data.MySqlClient;
using RantopadService.Conn;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace RantopadService.Business
{
    public class CommentBusiness
    {
        

        public static DataTable getAllComments(string wherelike)
        {
            string mysql = "SELECT * from comment  where CONTENT like '%{0}%'";
            mysql = string.Format(mysql, wherelike);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }

        public static DataTable getAllCommentsByOptionID(string optionID)
        {
            string mysql = "SELECT * from comment  where OPTION_ID = '{0}'";
            mysql = string.Format(mysql, optionID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }

        public static int getAllCommentsCount()
        {
            string mysql = "SELECT count(*) as count from comment ";
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            myconnection.Close();
            return count;
        }
        public static int getAllCommentsCount(string wherelike, string online)
        {
            string mysql = "SELECT count(CONTENT) as count from comment where CONTENT like '%{0}%' ";
            mysql = string.Format(mysql, wherelike);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            myconnection.Close();
            return count;
        }
        public static int checkCommentId(string adminID)
        {
            var count = checkData("ADMIN_NAME", adminID);
            return count;
        }
        public static int checkData(string colNM, string adminID)
        {
            string mysql = "SELECT count(ADMIN_NAME) as count from comment where {0} = '{1}' ";
            mysql = string.Format(mysql, colNM, adminID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            dr.Close();
            myconnection.Close();
            return count;
        }

        
        public static int delCommentByID(string ID)
        {
            string mysql = "delete from comment where ID='{0}'";
            mysql = string.Format(mysql, ID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

            var count = mycommand.ExecuteNonQuery();
            myconnection.Close();

            return count;
        }



        public static int addComment(string id, string userID, string commentText)
        {
            //INSERT INTO `rantopad`.`comment` (`ADMIN_NAME`, `ADMIN_PSW`, `EMAIL`, `ONLINE`) VALUES ('aaaaa', 'a', 'liang@163.com', '0');
            int count = 0;
            try
            {
                string mysql = "insert into `comment` (`OPTION_ID`, `CONTENT`, `USER_ID`, `TM`) VALUES ('{0}', '{1}', '{2}', '{3}') ";
                mysql = string.Format(mysql, id, commentText,userID,DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                //创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception)
            {

                count = 0;
            }


            return count;
        }

        internal static int updateComment( string name, string psd)
        {
            int count = 0;
            try
            {
                string mysql = "update `comment` set `ADMIN_PSW`='{1}'  where `ADMIN_NAME`='{0}'";
                mysql = string.Format(mysql, name, psd);
                //创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception)
            {

                count = 0;
            }


            return count;
        }
    }
}